Jassif Team Institution

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers WHERE address = 'Park Lane 38'", function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

C:\Users\Your Name>node demo_db_where.js

[
  { id: 11, name: 'Ben', address: 'Park Lane 38'}
]

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers WHERE address LIKE 'S%'", function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

C:\Users\Your Name>node demo_db_where_s.js

[
  { id: 8, name: 'Richard', address: 'Sky st 331'},
  { id: 14, name: 'Viola', address: 'Sideway 1633'}
]

// Address contains 'way'
con.query("SELECT * FROM customers WHERE address LIKE '%way%'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Name ends with 'y'
con.query("SELECT * FROM customers WHERE name LIKE '%y'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Name has exactly 3 characters
con.query("SELECT * FROM customers WHERE name LIKE '___'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

let adr = 'Mountain 21';
let sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(result);
});

let adr = 'Mountain 21';
let sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result) {
  if (err) throw err;
  console.log(result);
});

let name = 'Amy';
let adr = 'Mountain 21';
let sql = 'SELECT * FROM customers WHERE name = ? OR address = ?';
con.query(sql, [name, adr], function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Greater than
con.query("SELECT * FROM customers WHERE id > 5", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Less than or equal
con.query("SELECT * FROM customers WHERE id <= 10", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Not equal
con.query("SELECT * FROM customers WHERE name != 'John'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Between
con.query("SELECT * FROM customers WHERE id BETWEEN 5 AND 10", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// AND operator
con.query("SELECT * FROM customers WHERE name = 'John' AND address = 'Highway 71'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// OR operator
con.query("SELECT * FROM customers WHERE name = 'John' OR name = 'Amy'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// NOT operator
con.query("SELECT * FROM customers WHERE NOT name = 'John'", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// Combined conditions
con.query("SELECT * FROM customers WHERE (name = 'John' OR name = 'Amy') AND id > 5", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// IN operator with multiple values
con.query("SELECT * FROM customers WHERE name IN ('John', 'Amy', 'Hannah')", function (err, result) {
  if (err) throw err;
  console.log(result);
});

// NOT IN operator
con.query("SELECT * FROM customers WHERE name NOT IN ('John', 'Amy')", function (err, result) {
  if (err) throw err;
  console.log(result);
});

const mysql = require('mysql');

// Create connection
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "company_db"
});

// Connect to MySQL
con.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  
  console.log('Connected to MySQL database');
  
  // Example 1: Basic WHERE with exact match
  console.log('\n1. Customers with specific address:');
  const address = 'Park Lane 38';
  con.query("SELECT * FROM customers WHERE address = ?", [address], (err, result) => {
    if (err) {
      console.error('Error in query 1:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 2: WHERE with LIKE and wildcard
  console.log('\n2. Customers with address starting with S:');
  con.query("SELECT * FROM customers WHERE address LIKE 'S%'", (err, result) => {
    if (err) {
      console.error('Error in query 2:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 3: WHERE with multiple conditions
  console.log('\n3. Customers with complex conditions:');
  const minId = 5;
  const maxId = 10;
  const namePattern = '%y%';
  
  con.query(
    "SELECT * FROM customers WHERE id BETWEEN ? AND ? AND name LIKE ?",
    [minId, maxId, namePattern],
    (err, result) => {
      if (err) {
        console.error('Error in query 3:', err.message);
        return;
      }
      console.log(result);
    }
  );
  
  // Example 4: WHERE with IN operator
  console.log('\n4. Customers with specific names:');
  const names = ['John', 'Amy', 'Hannah'];
  con.query("SELECT * FROM customers WHERE name IN (?, ?, ?)", names, (err, result) => {
    if (err) {
      console.error('Error in query 4:', err.message);
      return;
    }
    console.log(result);
    
    // Close connection after all queries
    setTimeout(() => {
      con.end((err) => {
        if (err) {
          console.error('Error closing connection:', err.message);
          return;
        }
        console.log('\nConnection closed');
      });
    }, 1000);
  });
});